package com.kakame.codenews.base.web.dao;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.SingleColumnRowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import com.kakame.codenews.base.constant.SystemConstant;
import com.kakame.codenews.base.util.SystemUtil;
import com.kakame.codenews.base.web.annotation.ColumnMapping;
import com.kakame.codenews.base.web.annotation.TableMapping;
import com.kakame.codenews.base.web.fast.EntityGenerator;

/**
 * <pre>
 * <b>desc:</b> 所有dao层必须继承它 
 * <b>auth:</b> 何家明
 * <b>date:</b> 2018年6月6日 上午9:13:47
 * </pre>
 */
@SuppressWarnings("unchecked")
public class JdbcBaseDao {
    
    private Logger logger = LoggerFactory.getLogger(this.getClass().getPackage().getName());
    
    @Autowired
    @Qualifier("jdbcTemplate")
    private NamedParameterJdbcTemplate jdbcTemplate;
    
    /**
     * <pre>
     * <b>desc:</b> 更新操作 
     * <b>auth:</b> ex-hejm
     * <b>date:</b> 2018年6月7日 下午5:47:27
     * </pre>
     * @param sql
     * @param paramMap
     * @return
     */
    public int update(String sql, Map<String, Object> paramMap) {
        logSql(sql, paramMap);
        return jdbcTemplate.update(sql, paramMap);
    }
    /**
     * <pre>
     * <b>desc:</b> 更新操作 
     * <b>auth:</b> ex-hejm
     * <b>date:</b> 2018年6月7日 下午5:47:27
     * </pre>
     * @param sql
     * @param paramMap
     * @return
     */
    public <T> int update(String sql, T paramObj) {
        Map<String, Object> paramMap = SystemUtil.convertBeanToParamMap(paramObj);
        return update(sql, paramMap);
    }
    /**
     * <pre>
     * <b>desc:</b> 批量新增 
     * <b>auth:</b> ex-hejm
     * <b>date:</b> 2018年8月2日 下午1:54:51
     * </pre>
     * @param paramList
     * @return
     */
    public <T> int batchInsert(List<T> paramList) {
        T paramObj = paramList.get(0);
        Map<String, Object> paramMap = SystemUtil.convertBeanToParamMap(paramObj);
        String sql = generateInsertSql(paramObj, paramMap);
        
        Map<String, Object>[] batchValues = new LinkedHashMap[paramList.size()];
        for(int i = 0; i < paramList.size(); i++) {
            Map<String, Object> param = SystemUtil.convertBeanToParamMap(paramList.get(i));
            batchValues[i] = param;
        }
        
        int[] batchUpdate = jdbcTemplate.batchUpdate(sql, batchValues);
        return SystemUtil.sumArray(batchUpdate);
    }
    /**
     * <pre>
     * <b>desc:</b> 批量新增 
     * <b>auth:</b> ex-hejm
     * <b>date:</b> 2018年8月2日 下午1:54:51
     * </pre>
     * @param paramObj
     * @return
     */
    public <T> int insert(T paramObj) {
        Map<String, Object> paramMap = SystemUtil.convertBeanToParamMap(paramObj);
        String sql = generateInsertSql(paramObj, paramMap);
        return update(sql, paramMap);
    }
    /**
     * <pre>
     * <b>desc:</b> 拼接插入sql 
     * <b>auth:</b> ex-hejm
     * <b>date:</b> 2018年8月2日 下午2:36:06
     * </pre>
     * @param paramObj
     * @return
     */
    private <T> String generateInsertSql(T paramObj, Map<String, Object> paramMap) {
        StringBuilder sql = new StringBuilder();
        
        TableMapping tableMapping = paramObj.getClass().getAnnotation(TableMapping.class);
        sql.append("INSERT INTO ");
        sql.append(tableMapping.value()).append(" ( ");
        
        List<String> columns = new ArrayList<>();
        Field[] fields = paramObj.getClass().getDeclaredFields();
        for(Field field : fields){  
            boolean fieldHasAnno = field.isAnnotationPresent(ColumnMapping.class);  
            if(fieldHasAnno){  
                ColumnMapping fieldAnno = field.getAnnotation(ColumnMapping.class); 
                if(paramMap.get(field.getName()) != null) {
                    sql.append(fieldAnno.column()).append(", ");
                    columns.add(field.getName());
                }
            }  
        }
        sql.delete(sql.length() - 2, sql.length());
        sql.append(" ) VALUES ( ");
        
        for(int i = 0; i < columns.size(); i++) {
            sql.append(" :").append(columns.get(i)).append(", ");
        }
        sql.delete(sql.length() - 2, sql.length());
        sql.append(" ) ");
        return sql.toString();
    }
    
    /**
     * <pre>
     * <b>desc:</b> 查询操作 
     * <b>auth:</b> ex-hejm
     * <b>date:</b> 2018年6月7日 下午5:47:27
     * </pre>
     * @param sql
     * @param paramMap
     * @return
     */
    protected <T> List<T> list(String sql, Map<String, Object> paramMap, Class<T> clazz) {
    	if(StringUtils.isBlank(sql)) {
    		throw new IllegalArgumentException("sql语句不能为空");
    	}
    	if(SystemUtil.isBaseType(clazz)) {
    	    return jdbcTemplate.query(sql, paramMap, new SingleColumnRowMapper<T>());
    	}
    	
    	if(sql.indexOf(SystemConstant.SINGLE_TABLE_QUERY_ALL_COLUMN) != -1) {
    		StringBuilder sb = new StringBuilder();
    		Field[] fields = clazz.getDeclaredFields();
    		for(Field field : fields){  
                boolean fieldHasAnno = field.isAnnotationPresent(ColumnMapping.class);  
                if(fieldHasAnno){  
                	ColumnMapping fieldAnno = field.getAnnotation(ColumnMapping.class);  
                	sb.append(fieldAnno.column()).append(", ");
                }  
            }
    		if(sb.length() > 0) {
    			sb.delete(sb.length() - 2, sb.length());
    		}
    		sb.append(" ");
    		sql = sql.replace(SystemConstant.SINGLE_TABLE_QUERY_ALL_COLUMN, sb.toString());
    	}
    	if(sql.indexOf(SystemConstant.SINGLE_TABLE_QUERY_WHERE_CONDITION) != -1) {
    		StringBuilder sb = new StringBuilder();
    		Field[] fields = clazz.getDeclaredFields();
    		for(Field field : fields){  
                boolean fieldHasAnno = field.isAnnotationPresent(ColumnMapping.class);  
                if(fieldHasAnno){  
                	ColumnMapping fieldAnno = field.getAnnotation(ColumnMapping.class);  
                	if(paramMap.get(field.getName()) != null) {
                		sb.append(fieldAnno.column()).append(" = :").append(field.getName()).append(" AND ");
                	}
                }  
            }
    		if(sb.length() > 0) {
    			sb.delete(sb.length() - 5, sb.length());
    		}
    		sb.append(" ");
    		sql = sql.replace(SystemConstant.SINGLE_TABLE_QUERY_WHERE_CONDITION, sb.toString());
    	}
    	
		List<T> resultList = new ArrayList<T>();
    	
        logSql(sql, paramMap);
        List<Map<String,Object>> list = jdbcTemplate.queryForList(sql, paramMap);
        // 循环所有的属性
        Field[] fields = clazz.getDeclaredFields();
        for (Map<String, Object> map : list) {
        	T obj = null;
			try {
				obj = clazz.newInstance();
			} catch (InstantiationException e) {
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				e.printStackTrace();
			}
            // 把对应的值赋给obj的相应属性
        	Set<Entry<String,Object>> entrySet = map.entrySet();
        	Iterator<Entry<String, Object>> iterator = entrySet.iterator();
        	while(iterator.hasNext()) {
        		Entry<String, Object> next = iterator.next();
        		Object value = next.getValue();
        		String key = next.getKey();
                for (Field field : fields) {
                    // 允许操作私有属性
                    field.setAccessible(true);
                    // 获取当前属性的名字
                    String fieldName = field.getName();
                    String dbName = EntityGenerator.toDbName(fieldName);
                    if (dbName.equals(key)) {
                    	SystemUtil.setFieldValue(obj, value, field);
                    	break;
                    }
                }
        	}
        	resultList.add(obj);
		}
        return resultList;
    }
    /**
     * 根据传入的实体类进行查询
     * @author 何家明 2018年7月8日 下午5:57:09
     * @param sql
     * @param paramObj
     * @return
     */
	protected <T> List<T> list(String sql, T paramObj) {
    	Map<String, Object> paramMap = SystemUtil.convertBeanToParamMap(paramObj);
        return (List<T>) list(sql, paramMap, paramObj.getClass());
    }
    
    /**
     * <pre>
     * <b>desc:</b> 记录Sql日志 
     * <b>auth:</b> ex-hejm
     * <b>date:</b> 2018年6月8日 上午9:28:21
     * </pre>
     * @param sql
     * @param paramMap
     */
    private void logSql(String sql, Map<String, Object> paramMap) {
        Entry<String, Object> next;
        String valueStr;
        String key;
        Object value;
        
        String sqlMsg = sql;
        if(paramMap != null) {
            Iterator<Entry<String, Object>> iterator = paramMap.entrySet().iterator();
            while(iterator.hasNext()) {
                next = iterator.next();
                key = next.getKey();
                value = next.getValue();
                if(value instanceof Date) {
                    valueStr = DateFormatUtils.format((Date) value, "yyyy-MM-dd HH:mm:ss");
                } else {
                    valueStr = String.valueOf(value);
                }
                sqlMsg = sqlMsg.replace(":" + key, "'" + valueStr + "'");
            }
        }
        logger.info("请求SQL: " + sqlMsg);
    }
}
